package com.changil.nowonchangil.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.changil.nowonchangil.dto.Article;
import com.changil.nowonchangil.dto.AttachmentImage;

public class ArticleManager extends BaseTableManager{
	private static ArticleManager instance = null;
	final static String TABLE_NAME = "Article";
	static enum  COLUMNS {
		id,
		boardId,
		title,
		content,
		userId,
		writtenDateTime
	}
	
	public static ArticleManager getsInstance() {
		if (instance == null) {
			synchronized (ArticleManager.class) {
				if (instance == null) { 
					instance = new ArticleManager();
				}
			}
		}
		return instance;
	}
	
	public ArrayList<Article> select(int boardId, int fromId, int count, boolean isDownSide) {
		String direction = null;
		if(isDownSide) {
			direction = "<";
		} else {
			direction = ">";
		}
		String sql = "SELECT * FROM " 
					+ ArticleManager.TABLE_NAME+","+UserManager.TABLE_NAME	
				+" WHERE "+TABLE_NAME+"."+COLUMNS.userId.toString()+"="+UserManager.TABLE_NAME+"."+UserManager.COLUMNS.id.toString()
						+" AND "+TABLE_NAME+"."+COLUMNS.id.toString() + direction +"?"
						+" AND "+COLUMNS.boardId.toString()+"=?"
				+" ORDER BY "+COLUMNS.writtenDateTime.toString() + " DESC"
				+" LIMIT ?";
		 
		Connection conn = createConnection();
		PreparedStatement statement = null;
		ResultSet rs = null;
		ArrayList<Article> list = new ArrayList<Article>();
		try {
			statement = conn.prepareStatement(sql);
			statement.setInt(1, fromId);
			statement.setInt(2, boardId);
			statement.setInt(3, count);
			rs = statement.executeQuery();
			int index = 0; 
			while (rs.next()){
				Article article = new Article();
				article.id = rs.getInt(rs.findColumn(COLUMNS.id.toString()));
					article.boardId = rs.getInt(rs.findColumn(COLUMNS.boardId.toString()));
					article.title = rs.getString(rs.findColumn(COLUMNS.title.toString()));
					article.content = rs.getString(rs.findColumn(COLUMNS.content.toString()));
					article.writer.id = rs.getString(rs.findColumn(COLUMNS.userId.toString()));
					article.writer.name = rs.getString(rs.findColumn(UserManager.COLUMNS.name.toString()));
					article.writer.joinDateTime = rs.getString(rs.findColumn(UserManager.COLUMNS.joinDateTime.toString()));
					article.writtenDateTime = rs.getString(rs.findColumn(COLUMNS.writtenDateTime.toString()));
					Pattern p = Pattern.compile("<img[^>]*src=[\"']?([^>\"']+)[\"']?[^>]*>");
					Matcher m = p.matcher(article.content);
					while (m.find()) {
						AttachmentImage image = new AttachmentImage();
						image.url = m.group(1);
						article.list.list.add(image);
					}
					article.list.size = article.list.list.size();
					list.add(article);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  finally {
			if(null != rs) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(null != statement) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		return list;
	}
	
	public int insert(int boardId, String title, String content) {
		String sql = "INSERT INTO "
					+TABLE_NAME
					+"("
						+COLUMNS.boardId.toString()+","
						+COLUMNS.title.toString()+","
						+COLUMNS.content.toString()+","
						+COLUMNS.userId.toString()
					+") "
					+"VALUES (?,?,?,?)";
		int result = -1;
		Connection conn = createConnection();
		PreparedStatement statement = null;
		try {
			statement = conn.prepareStatement(sql);
			statement.setInt(1, boardId);
			statement.setString(2, title);
			statement.setString(3, content);
			statement.setString(4, "changil153");
			statement.executeUpdate();
			sql = "SELECT LAST_INSERT_ID()";
			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			if(rs.next()) {
				result = rs.getInt(1);
			}
			
		} catch (SQLException e) {
			System.out.println(sql);
			e.printStackTrace();
		}  finally {
			if(null != statement) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	
	public int update(int targetId, String newTitle, String newContent) {
		String sql = "UPDATE "
					+TABLE_NAME
					+" SET "
					+COLUMNS.title.toString()+"=?,"
					+COLUMNS.content.toString()+"=? "
					+"WHERE "
					+COLUMNS.id.toString()+"=?";
		int result = -1;
		
		Connection conn = createConnection();
		PreparedStatement statement = null;
		try {
			statement = conn.prepareStatement(sql);
			statement.setString(1, newTitle);
			statement.setString(2, newContent);
			statement.setInt(3, targetId);
			result = statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(null != statement) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	
	public int delete(int targetId) {
		String sql = "DELETE FROM "
					+TABLE_NAME
					+" WHERE "
					+COLUMNS.id.toString()+"=?";
		int result = -1;
		 
		Connection conn = createConnection();
		PreparedStatement statement = null;
		try {
			statement = conn.prepareStatement(sql);
			statement.setInt(1, targetId);
			 
			result = statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(null != statement) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	
	
	 

}
